using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AuthorizeSession(SqlGuid SessionGuid)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string sessionCommandText = "SELECT COUNT(UserGuid) FROM Security_Session WHERE SessionGuid = @SessionGuid AND DATEDIFF(MI, ProcessDateTime, GetDate()) <= 45 ";

                SqlCommand sessionCommand = new SqlCommand(sessionCommandText, connection);

                sessionCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));

                int sessionCount = (int)sessionCommand.ExecuteScalar();

                if (sessionCount == 1)
                {
                    string sessionActivityCommandText = "INSERT INTO Security_SessionActivity (SessionGuid) VALUES (@SessionGuid)";

                    SqlCommand sessionActivityCommand = new SqlCommand(sessionActivityCommandText, connection);

                    sessionActivityCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));

                    sessionActivityCommand.ExecuteNonQuery();

                    string getSessionActivityGuidCommandText = "SELECT TOP 1 SessionActivityGuid FROM Security_SessionActivity WHERE SessionGuid = @SessionGuid ORDER BY ProcessDateTime DESC";

                    SqlCommand getSessionActivityGuidCommand = new SqlCommand(getSessionActivityGuidCommandText, connection);

                    getSessionActivityGuidCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));

                    SqlContext.Pipe.ExecuteAndSend(getSessionActivityGuidCommand);
                }
                else
                {
                    SqlContext.Pipe.ExecuteAndSend(new SqlCommand("SELECT CAST('00000000-0000-0000-0000-000000000000' AS UNIQUEIDENTIFIER) AS SessionActivityGuid", connection));
                }
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
